﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.SysTools;
using GovernmentOfficeProject.LevelDataAccessModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_COM_Sms_Ready
    {

        public Entity_COM_Sms_Ready getSmsReadyBySRID(int srID)
        {
            try
            {
                string strSQL = "select * from [COM_SMS_READY] where id=@srID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@srID",srID)
                };
                DataSet dst = DAL_DBGM.GetDataSet(strSQL,values);
                Entity_COM_Sms_Ready smsRdy = null;
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    smsRdy = new Entity_COM_Sms_Ready();
                    smsRdy.RdyID = Convert.ToDecimal(row["id"]);
                    smsRdy.RdyFromUserID = Convert.ToDecimal(row["Sms_FromUserID"]);
                    smsRdy.RdyToUserID = Convert.ToDecimal(row["Sms_ToUserID"]);
                    smsRdy.RdyAccessType = Convert.ToString(row["Sms_AccessType"]);
                    smsRdy.RdyAccessValue = Convert.ToDecimal(row["Sms_AccessValue"]);
                    smsRdy.RdyMtnumber = Convert.ToString(row["Sms_MtNumber"]);
                    smsRdy.RdySendTime = Convert.ToString(row["Sms_SendTime"]);
                    smsRdy.RdyReadySendTime = Convert.ToString(row["Sms_ReadySendTime"]);
                    smsRdy.RdyBeforeMinute = Convert.ToInt32(row["Sms_BeforeMinute"]);
                    smsRdy.RdyTextContent = Convert.ToString(row["Sms_TextContent"]);
                    smsRdy.RdySendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsRdy.RdySendType = Convert.ToInt32(row["Sms_SendType"]);
                    smsRdy.Backup1 = Convert.ToString(row["Backup1"]);
                    smsRdy.Backup2 = Convert.ToString(row["Backup2"]);
                    smsRdy.Backup3 = Convert.ToString(row["Backup3"]);
                }
                return smsRdy;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM_Sms_Ready.getSmsReadyBySRID(int srID)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_COM_Sms_Ready> getSmsReadyList()
        {
            try
            {
                string strSQL = "select * from [COM_SMS_READY]";
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_COM_Sms_Ready smsRdy = null;
                List<Entity_COM_Sms_Ready> smsRdyList = new List<Entity_COM_Sms_Ready>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    smsRdy = new Entity_COM_Sms_Ready();
                    smsRdy.RdyID = Convert.ToDecimal(row["id"]);
                    smsRdy.RdyFromUserID = Convert.ToDecimal(row["Sms_FromUserID"]);
                    smsRdy.RdyToUserID = Convert.ToDecimal(row["Sms_ToUserID"]);
                    smsRdy.RdyAccessType = Convert.ToString(row["Sms_AccessType"]);
                    smsRdy.RdyAccessValue = Convert.ToDecimal(row["Sms_AccessValue"]);
                    smsRdy.RdyMtnumber = Convert.ToString(row["Sms_MtNumber"]);
                    smsRdy.RdySendTime = Convert.ToString(row["Sms_SendTime"]);
                    smsRdy.RdyReadySendTime = Convert.ToString(row["Sms_ReadySendTime"]);
                    smsRdy.RdyBeforeMinute = Convert.ToInt32(row["Sms_BeforeMinute"]);
                    smsRdy.RdyTextContent = Convert.ToString(row["Sms_TextContent"]);
                    smsRdy.RdySendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsRdy.RdySendType = Convert.ToInt32(row["Sms_SendType"]);
                    smsRdy.Backup1 = Convert.ToString(row["Backup1"]);
                    smsRdy.Backup2 = Convert.ToString(row["Backup2"]);
                    smsRdy.Backup3 = Convert.ToString(row["Backup3"]);
                    smsRdyList.Add(smsRdy);
                }
                return smsRdyList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM_Sms_Ready.getSmsReadyList()---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_COM_Sms_Ready> getSmsReadyListBysmsRdyEntity(Entity_COM_Sms_Ready smsRdyEntity)
        {
            try
            {
                string strSQL = "select * from [COM_SMS_READY] where 1=1";
                if (smsRdyEntity.RdyID != 0)
                {
                    strSQL += " and id=" + smsRdyEntity.RdyID;
                }
                if (smsRdyEntity.RdyFromUserID != 0)
                {
                    strSQL += " and Sms_FromUserID=" + smsRdyEntity.RdyFromUserID;
                }
                if (smsRdyEntity.RdyToUserID!=0)
                {
                    strSQL += " and Sms_ToUserID=" + smsRdyEntity.RdyToUserID;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyAccessType))
                {
                    strSQL += " and Sms_AccessType='" + smsRdyEntity.RdyAccessType + "'";
                }
                if (smsRdyEntity.RdyAccessValue!=0)
                {
                    strSQL += " and Sms_AccessValue=" + smsRdyEntity.RdyAccessValue;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyMtnumber))
                {
                    strSQL += " and Sms_MtNumber='" + smsRdyEntity.RdyMtnumber + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdySendTime))
                {
                    strSQL += " and Sms_SendTime='" + smsRdyEntity.RdySendTime + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyReadySendTime))
                {
                    strSQL += " and Sms_ReadySendTime='" + smsRdyEntity.RdyReadySendTime + "'";
                }
                if (smsRdyEntity.RdyBeforeMinute>=0)
                {
                    strSQL += " and Sms_BeforeMinute=" + smsRdyEntity.RdyBeforeMinute;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyTextContent))
                {
                    strSQL += " and Sms_TextContent='" + smsRdyEntity.RdyTextContent + "'";
                }
                if (smsRdyEntity.RdySendFlag>=0)
                {
                    strSQL += " and Sms_SendFlag=" + smsRdyEntity.RdySendFlag;
                }
                if (smsRdyEntity.RdySendType>=0)
                {
                    strSQL += " and Sms_SendType=" + smsRdyEntity.RdySendType;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup1))
                {
                    strSQL += " and Backup1='" + smsRdyEntity.Backup1 + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup2))
                {
                    strSQL += " and Backup2='" + smsRdyEntity.Backup2 + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup3))
                {
                    strSQL += " and Backup3='" + smsRdyEntity.Backup3 + "'";
                }
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_COM_Sms_Ready smsRdy = null;
                List<Entity_COM_Sms_Ready> smsRdyList = new List<Entity_COM_Sms_Ready>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    smsRdy = new Entity_COM_Sms_Ready();
                    smsRdy.RdyID = Convert.ToDecimal(row["id"]);
                    smsRdy.RdyFromUserID = Convert.ToDecimal(row["Sms_FromUserID"]);
                    smsRdy.RdyToUserID = Convert.ToDecimal(row["Sms_ToUserID"]);
                    smsRdy.RdyAccessType = Convert.ToString(row["Sms_AccessType"]);
                    smsRdy.RdyAccessValue = Convert.ToDecimal(row["Sms_AccessValue"]);
                    smsRdy.RdyMtnumber = Convert.ToString(row["Sms_MtNumber"]);
                    smsRdy.RdySendTime = Convert.ToString(row["Sms_SendTime"]);
                    smsRdy.RdyReadySendTime = Convert.ToString(row["Sms_ReadySendTime"]);
                    smsRdy.RdyBeforeMinute = Convert.ToInt32(row["Sms_BeforeMinute"]);
                    smsRdy.RdyTextContent = Convert.ToString(row["Sms_TextContent"]);
                    smsRdy.RdySendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsRdy.RdySendType = Convert.ToInt32(row["Sms_SendType"]);
                    smsRdy.Backup1 = Convert.ToString(row["Backup1"]);
                    smsRdy.Backup2 = Convert.ToString(row["Backup2"]);
                    smsRdy.Backup3 = Convert.ToString(row["Backup3"]);
                    smsRdyList.Add(smsRdy);
                }
                return smsRdyList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM_Sms_Ready.getSmsReadyListBysmsRdyEntity(Entity_COM_Sms_Ready smsRdyEntity)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }


        //select * from COM_SMS_READY where Sms_SendTime>='2013-01-21 00:00:01' and Sms_SendTime <= '2013-01-21 23:59:59'

        /// <summary>
        /// 根据传入的sms实体条件和信息发送时间的间隔,获取SMS列表.
        /// </summary>
        /// <param name="smsEntity"></param>
        /// <returns></returns>
        public List<Entity_COM_Sms_Ready> getSmsListBySmsEntityAndSendTimeStartEnd(Entity_COM_Sms_Ready smsRdyEntity, string startTime, string endTime)
        {
            try
            {
                string strSQL = "select * from [COM_SMS_READY] where 1=1";
                if (smsRdyEntity.RdyID != 0)
                {
                    strSQL += " and id=" + smsRdyEntity.RdyID;
                }
                if (smsRdyEntity.RdyFromUserID != 0)
                {
                    strSQL += " and Sms_FromUserID=" + smsRdyEntity.RdyFromUserID;
                }
                if (smsRdyEntity.RdyToUserID != 0)
                {
                    strSQL += " and Sms_ToUserID=" + smsRdyEntity.RdyToUserID;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyAccessType))
                {
                    strSQL += " and Sms_AccessType='" + smsRdyEntity.RdyAccessType + "'";
                }
                if (smsRdyEntity.RdyAccessValue != 0)
                {
                    strSQL += " and Sms_AccessValue=" + smsRdyEntity.RdyAccessValue;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyMtnumber))
                {
                    strSQL += " and Sms_MtNumber='" + smsRdyEntity.RdyMtnumber + "'";
                }
                //if (smsRdyEntity.SmsSendTime != "" && smsRdyEntity.SmsSendTime != null)
                //{
                //    strSQL += " and Sms_SendTime between convert(DateTime,'" + Convert.ToDateTime(startTime).ToString() + "') and convert(DateTime,'" + Convert.ToDateTime(endTime).ToString() + "')";
                //}
                if (startTime != "" && endTime != "")
                {
                    //Sms_SendTime>='2013-01-21 00:00:01' and Sms_SendTime <= '2013-01-21 23:59:59'
                    strSQL += " and Sms_SendTime>='" + startTime + "' and Sms_SendTime <= '" + endTime + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyReadySendTime))
                {
                    strSQL += " and Sms_ReadySendTime='" + smsRdyEntity.RdyReadySendTime + "'";
                }
                if (smsRdyEntity.RdyBeforeMinute >= 0)
                {
                    strSQL += " and Sms_BeforeMinute=" + smsRdyEntity.RdyBeforeMinute;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyTextContent))
                {
                    strSQL += " and Sms_TextContent='" + smsRdyEntity.RdyTextContent + "'";
                }
                if (smsRdyEntity.RdySendFlag >= 0)
                {
                    strSQL += " and Sms_SendFlag=" + smsRdyEntity.RdySendFlag;
                }
                if (smsRdyEntity.RdySendType >= 0)
                {
                    strSQL += " and Sms_SendType=" + smsRdyEntity.RdySendType;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup1))
                {
                    strSQL += " and Backup1='" + smsRdyEntity.Backup1 + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup2))
                {
                    strSQL += " and Backup2='" + smsRdyEntity.Backup2 + "'";
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup3))
                {
                    strSQL += " and Backup3='" + smsRdyEntity.Backup3 + "'";
                }
                //AppLog.Write("根据时间段获取信息发送日志SQL:" + strSQL, AppLog.LogMessageType.Debug);
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_COM_Sms_Ready smsRdy = null;
                List<Entity_COM_Sms_Ready> smsRdyList = new List<Entity_COM_Sms_Ready>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    smsRdy = new Entity_COM_Sms_Ready();
                    smsRdy.RdyID = Convert.ToDecimal(row["id"]);
                    smsRdy.RdyFromUserID = Convert.ToDecimal(row["Sms_FromUserID"]);
                    smsRdy.RdyToUserID = Convert.ToDecimal(row["Sms_ToUserID"]);
                    smsRdy.RdyAccessType = Convert.ToString(row["Sms_AccessType"]);
                    smsRdy.RdyAccessValue = Convert.ToDecimal(row["Sms_AccessValue"]);
                    smsRdy.RdyMtnumber = Convert.ToString(row["Sms_MtNumber"]);
                    smsRdy.RdySendTime = Convert.ToString(row["Sms_SendTime"]);
                    smsRdy.RdyReadySendTime = Convert.ToString(row["Sms_ReadySendTime"]);
                    smsRdy.RdyBeforeMinute = Convert.ToInt32(row["Sms_BeforeMinute"]);
                    smsRdy.RdyTextContent = Convert.ToString(row["Sms_TextContent"]);
                    smsRdy.RdySendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsRdy.RdySendType = Convert.ToInt32(row["Sms_SendType"]);
                    smsRdy.Backup1 = Convert.ToString(row["Backup1"]);
                    smsRdy.Backup2 = Convert.ToString(row["Backup2"]);
                    smsRdy.Backup3 = Convert.ToString(row["Backup3"]);
                    smsRdyList.Add(smsRdy);
                }
                return smsRdyList;                
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.getSmsListBySmsEntityAndSendTimeStartEnd(Entity_COM_Sms smsEntity, string startTime, string endTime)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 添加一条smsReady信息.
        /// </summary>
        /// <param name="smsEntity"></param>
        /// <returns></returns>
        public bool addSmsReday(Entity_COM_Sms_Ready smsRdyEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                StringBuilder sqlBudIns = new StringBuilder();
                StringBuilder sqlBudValue = new StringBuilder();
                sqlBudIns.Append("insert into [COM_SMS_READY](");
                sqlBudValue.Append(" values(");
                List<SqlParameter> sprList = new List<SqlParameter>();

                if (smsRdyEntity.RdyID != 0)
                {
                    sqlBudIns.Append("id,");
                    sqlBudValue.Append("@rdyID,");
                    sprList.Add(new SqlParameter("@rdyID", smsRdyEntity.RdyID));
                    blState = true;
                }
                if (smsRdyEntity.RdyFromUserID != 0)
                {
                    sqlBudIns.Append("Sms_FromUserID,");
                    sqlBudValue.Append("@fromUID,");
                    sprList.Add(new SqlParameter("@fromUID", smsRdyEntity.RdyFromUserID));
                    blState = true;
                }
                if (smsRdyEntity.RdyToUserID != 0)
                {
                    sqlBudIns.Append("Sms_ToUserID,");
                    sqlBudValue.Append("@toUID,");
                    sprList.Add(new SqlParameter("@toUID", smsRdyEntity.RdyToUserID));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyAccessType))
                {
                    sqlBudIns.Append("Sms_AccessType,");
                    sqlBudValue.Append("@accessType,");
                    sprList.Add(new SqlParameter("@accessType", smsRdyEntity.RdyAccessType));
                    blState = true;
                }
                if (smsRdyEntity.RdyAccessValue != 0)
                {
                    sqlBudIns.Append("Sms_AccessValue,");
                    sqlBudValue.Append("@accessValue,");
                    sprList.Add(new SqlParameter("@accessValue", smsRdyEntity.RdyAccessValue));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyMtnumber))
                {
                    sqlBudIns.Append("Sms_MtNumber,");
                    sqlBudValue.Append("@mtNumber,");
                    sprList.Add(new SqlParameter("@mtNumber", smsRdyEntity.RdyMtnumber));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdySendTime))
                {
                    sqlBudIns.Append("Sms_SendTime,");
                    sqlBudValue.Append("@sendTime,");
                    sprList.Add(new SqlParameter("@sendTime", smsRdyEntity.RdySendTime));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyReadySendTime))
                {
                    sqlBudIns.Append("Sms_ReadySendTime,");
                    sqlBudValue.Append("@readySendTime,");
                    sprList.Add(new SqlParameter("@readySendTime", smsRdyEntity.RdyReadySendTime));
                    blState = true;
                }
                if (smsRdyEntity.RdyBeforeMinute >= 0)
                {
                    sqlBudIns.Append("Sms_BeforeMinute,");
                    sqlBudValue.Append("@beforeMin,");
                    sprList.Add(new SqlParameter("@beforeMin", smsRdyEntity.RdyBeforeMinute));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.RdyTextContent))
                {
                    sqlBudIns.Append("Sms_TextContent,");
                    sqlBudValue.Append("@textContent,");
                    sprList.Add(new SqlParameter("@textContent", smsRdyEntity.RdyTextContent));
                    blState = true;
                }
                if (smsRdyEntity.RdySendFlag >= 0)
                {
                    sqlBudIns.Append("Sms_SendFlag,");
                    sqlBudValue.Append("@sendFlag,");
                    sprList.Add(new SqlParameter("@sendFlag", smsRdyEntity.RdySendFlag));
                    blState = true;
                }
                if (smsRdyEntity.RdySendType >= 0)
                {
                    sqlBudIns.Append("Sms_SendType,");
                    sqlBudValue.Append("@sendType,");
                    sprList.Add(new SqlParameter("@sendType", smsRdyEntity.RdySendType));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup1))
                {
                    sqlBudIns.Append("Backup1,");
                    sqlBudValue.Append("@backup1,");
                    sprList.Add(new SqlParameter("@backup1", smsRdyEntity.Backup1));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup2))
                {
                    sqlBudIns.Append("Backup2,");
                    sqlBudValue.Append("@backup2,");
                    sprList.Add(new SqlParameter("@backup2", smsRdyEntity.Backup2));
                    blState = true;
                }
                if (!String.IsNullOrEmpty(smsRdyEntity.Backup3))
                {
                    sqlBudIns.Append("Backup3,");
                    sqlBudValue.Append("@backup3,");
                    sprList.Add(new SqlParameter("@backup3", smsRdyEntity.Backup3));
                    blState = true;
                }

                if (blState)
                {
                    string strSQL = sqlBudIns.ToString().Substring(0, sqlBudIns.Length - 1) + ")" + sqlBudValue.ToString().Substring(0, sqlBudValue.Length - 1) + ")";
                    SqlParameter[] values = sprList.ToArray();
                    AppLog.Write("DAL-增加smsReady的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //添加用户失败:没有任何数据.
                    AppLog.Write("添加smsReady失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag; 
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM_Sms_Ready.addSmsReday(Entity_COM_Sms_Ready smsRdyEntity)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool deleteSmsReadyBySRID(int srID)
        {
            try
            {
                string strSQL = "delete from [COM_SMS_READY] where id=@smsRID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@smsRID",srID)
                };
                return DAL_DBGM.DeleteInfo(strSQL, values);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM_Sms_Ready.deleteSmsReadyBySRID(int srID)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool deleteSmsReadyByCalendarID(decimal calendarId)
        {
            try
            {
                string strSQL = @"delete from [COM_SMS_READY] where Sms_AccessType = 'SME_SCHEDULE' and Sms_AccessValue = {0}".FormatWith(calendarId);
                return DAL_DBGM.DeleteInfo(strSQL);
            }
            catch(Exception e)
            {
                AppLog.Write(e.Message, SysTools.AppLog.LogMessageType.Error);
                throw e;
            }
            
        }

        public bool updateSmsReadyBySRIDAndSREntity(decimal srID, Entity_COM_Sms_Ready smsRdyEntity)
        {
            try
            {
                string sql = @"update [COM_SMS_READY] set  Sms_FromUserID={0},  Sms_ToUserID={1},  Sms_AccessType='{2}',  Sms_AccessValue={3},  Sms_MtNumber='{4}',  Sms_SendTime='{5}',  Sms_ReadySendTime='{6}',  Sms_BeforeMinute={7},  Sms_TextContent='{8}',  Sms_SendFlag={9},  Sms_SendType={10} where id={11}";
                sql = sql.FormatWith(smsRdyEntity.RdyFromUserID,
                                     smsRdyEntity.RdyToUserID,
                                     smsRdyEntity.RdyAccessType,
                                     smsRdyEntity.RdyAccessValue,
                                     smsRdyEntity.RdyMtnumber,
                                     smsRdyEntity.RdySendTime,
                                     smsRdyEntity.RdyReadySendTime,
                                     smsRdyEntity.RdyBeforeMinute,
                                     smsRdyEntity.RdyTextContent,
                                     smsRdyEntity.RdySendFlag,
                                     smsRdyEntity.RdySendType,
                                     srID
                                     );
                return DAL_DBGM.UpdateInfo(sql);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---异常位置:DAL_COM_Sms_Ready.updateSmsReadyBySRIDAndSREntity(decimal srID, Entity_COM_Sms_Ready smsRdyEntity)---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }

    }

}
